package com.school.dao;

import com.school.entity.Dormitory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * 宿舍DAO
 */
@Repository
public class DormitoryDao {
    
    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    
    /**
     * 插入宿舍
     */
    public Long insert(Dormitory dormitory) {
        String sql = "INSERT INTO tb_dormitory (building, room_number, floor, capacity, occupied, status, remark) " +
                "VALUES (:building, :roomNumber, :floor, :capacity, :occupied, :status, :remark)";
        
        MapSqlParameterSource params = new MapSqlParameterSource()
                .addValue("building", dormitory.getBuilding())
                .addValue("roomNumber", dormitory.getRoomNumber())
                .addValue("floor", dormitory.getFloor())
                .addValue("capacity", dormitory.getCapacity())
                .addValue("occupied", dormitory.getOccupied())
                .addValue("status", dormitory.getStatus())
                .addValue("remark", dormitory.getRemark());
        
        KeyHolder keyHolder = new GeneratedKeyHolder();
        namedParameterJdbcTemplate.update(sql, params, keyHolder);
        return keyHolder.getKey().longValue();
    }
    
    /**
     * 更新宿舍
     */
    public int update(Dormitory dormitory) {
        String sql = "UPDATE tb_dormitory SET floor = :floor, capacity = :capacity, occupied = :occupied, " +
                "status = :status, remark = :remark WHERE id = :id";
        
        MapSqlParameterSource params = new MapSqlParameterSource()
                .addValue("id", dormitory.getId())
                .addValue("floor", dormitory.getFloor())
                .addValue("capacity", dormitory.getCapacity())
                .addValue("occupied", dormitory.getOccupied())
                .addValue("status", dormitory.getStatus())
                .addValue("remark", dormitory.getRemark());
        
        return namedParameterJdbcTemplate.update(sql, params);
    }
    
    /**
     * 删除宿舍
     */
    public int deleteById(Long id) {
        String sql = "DELETE FROM tb_dormitory WHERE id = :id";
        MapSqlParameterSource params = new MapSqlParameterSource("id", id);
        return namedParameterJdbcTemplate.update(sql, params);
    }
    
    /**
     * 批量删除宿舍
     */
    public int batchDelete(List<Long> ids) {
        String sql = "DELETE FROM tb_dormitory WHERE id IN (:ids)";
        MapSqlParameterSource params = new MapSqlParameterSource("ids", ids);
        return namedParameterJdbcTemplate.update(sql, params);
    }
    
    /**
     * 根据ID查询
     */
    public Dormitory findById(Long id) {
        String sql = "SELECT * FROM tb_dormitory WHERE id = :id";
        MapSqlParameterSource params = new MapSqlParameterSource("id", id);
        List<Dormitory> list = namedParameterJdbcTemplate.query(sql, params, 
                (rs, rowNum) -> {
                    Dormitory d = new Dormitory();
                    d.setId(rs.getLong("id"));
                    d.setBuilding(rs.getString("building"));
                    d.setRoomNumber(rs.getString("room_number"));
                    d.setFloor(rs.getInt("floor"));
                    d.setCapacity(rs.getInt("capacity"));
                    d.setOccupied(rs.getInt("occupied"));
                    d.setStatus(rs.getString("status"));
                    d.setRemark(rs.getString("remark"));
                    d.setCreateTime(rs.getTimestamp("create_time"));
                    d.setUpdateTime(rs.getTimestamp("update_time"));
                    return d;
                });
        return list.isEmpty() ? null : list.get(0);
    }
    
    /**
     * 根据楼栋和宿舍号查询
     */
    public Dormitory findByBuildingAndRoom(String building, String roomNumber) {
        String sql = "SELECT * FROM tb_dormitory WHERE building = :building AND room_number = :roomNumber";
        MapSqlParameterSource params = new MapSqlParameterSource()
                .addValue("building", building)
                .addValue("roomNumber", roomNumber);
        List<Dormitory> list = namedParameterJdbcTemplate.query(sql, params, 
                (rs, rowNum) -> {
                    Dormitory d = new Dormitory();
                    d.setId(rs.getLong("id"));
                    d.setBuilding(rs.getString("building"));
                    d.setRoomNumber(rs.getString("room_number"));
                    d.setFloor(rs.getInt("floor"));
                    d.setCapacity(rs.getInt("capacity"));
                    d.setOccupied(rs.getInt("occupied"));
                    d.setStatus(rs.getString("status"));
                    d.setRemark(rs.getString("remark"));
                    d.setCreateTime(rs.getTimestamp("create_time"));
                    d.setUpdateTime(rs.getTimestamp("update_time"));
                    return d;
                });
        return list.isEmpty() ? null : list.get(0);
    }
    
    /**
     * 查询所有宿舍
     */
    public List<Dormitory> findAll() {
        String sql = "SELECT * FROM tb_dormitory ORDER BY building, CAST(room_number AS SIGNED)";
        return namedParameterJdbcTemplate.query(sql, 
                (rs, rowNum) -> {
                    Dormitory d = new Dormitory();
                    d.setId(rs.getLong("id"));
                    d.setBuilding(rs.getString("building"));
                    d.setRoomNumber(rs.getString("room_number"));
                    d.setFloor(rs.getInt("floor"));
                    d.setCapacity(rs.getInt("capacity"));
                    d.setOccupied(rs.getInt("occupied"));
                    d.setStatus(rs.getString("status"));
                    d.setRemark(rs.getString("remark"));
                    d.setCreateTime(rs.getTimestamp("create_time"));
                    d.setUpdateTime(rs.getTimestamp("update_time"));
                    return d;
                });
    }
    
    /**
     * 分页查询
     */
    public List<Dormitory> findByPage(Map<String, Object> params) {
        StringBuilder sql = new StringBuilder("SELECT * FROM tb_dormitory WHERE 1=1");
        
        if (params.get("building") != null && !"".equals(params.get("building"))) {
            sql.append(" AND building = :building");
        }
        if (params.get("roomNumber") != null && !"".equals(params.get("roomNumber"))) {
            sql.append(" AND room_number LIKE CONCAT('%', :roomNumber, '%')");
        }
        if (params.get("status") != null && !"".equals(params.get("status"))) {
            sql.append(" AND status = :status");
        }
        
        sql.append(" ORDER BY building, CAST(room_number AS SIGNED) LIMIT :limit OFFSET :offset");
        
        return namedParameterJdbcTemplate.query(sql.toString(), params, 
                (rs, rowNum) -> {
                    Dormitory d = new Dormitory();
                    d.setId(rs.getLong("id"));
                    d.setBuilding(rs.getString("building"));
                    d.setRoomNumber(rs.getString("room_number"));
                    d.setFloor(rs.getInt("floor"));
                    d.setCapacity(rs.getInt("capacity"));
                    d.setOccupied(rs.getInt("occupied"));
                    d.setStatus(rs.getString("status"));
                    d.setRemark(rs.getString("remark"));
                    d.setCreateTime(rs.getTimestamp("create_time"));
                    d.setUpdateTime(rs.getTimestamp("update_time"));
                    return d;
                });
    }
    
    /**
     * 统计总数
     */
    public long count(Map<String, Object> params) {
        StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM tb_dormitory WHERE 1=1");
        
        if (params.get("building") != null && !"".equals(params.get("building"))) {
            sql.append(" AND building = :building");
        }
        if (params.get("roomNumber") != null && !"".equals(params.get("roomNumber"))) {
            sql.append(" AND room_number LIKE CONCAT('%', :roomNumber, '%')");
        }
        if (params.get("status") != null && !"".equals(params.get("status"))) {
            sql.append(" AND status = :status");
        }
        
        Long count = namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Long.class);
        return count != null ? count : 0L;
    }
    
    /**
     * 查询所有可用的宿舍（状态为可用且有空余床位）
     */
    public List<Dormitory> findAvailableDormitories() {
        String sql = "SELECT * FROM tb_dormitory WHERE status = '可用' AND occupied < capacity " +
                "ORDER BY building, CAST(room_number AS SIGNED)";
        return namedParameterJdbcTemplate.query(sql, 
                (rs, rowNum) -> {
                    Dormitory d = new Dormitory();
                    d.setId(rs.getLong("id"));
                    d.setBuilding(rs.getString("building"));
                    d.setRoomNumber(rs.getString("room_number"));
                    d.setFloor(rs.getInt("floor"));
                    d.setCapacity(rs.getInt("capacity"));
                    d.setOccupied(rs.getInt("occupied"));
                    d.setStatus(rs.getString("status"));
                    d.setRemark(rs.getString("remark"));
                    d.setCreateTime(rs.getTimestamp("create_time"));
                    d.setUpdateTime(rs.getTimestamp("update_time"));
                    return d;
                });
    }
    
    /**
     * 更新宿舍已入住人数
     */
    public int updateOccupied(Long id, Integer occupied) {
        String sql = "UPDATE tb_dormitory SET occupied = :occupied WHERE id = :id";
        MapSqlParameterSource params = new MapSqlParameterSource()
                .addValue("id", id)
                .addValue("occupied", occupied);
        return namedParameterJdbcTemplate.update(sql, params);
    }
}


